﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;

namespace EnergyConsumptionMonitorSystem.Models
{
    public class DBManager
    {
        private string _connectionString = ConfigurationManager.ConnectionStrings["ecmsConnection"].ConnectionString;
        public string ConnectionString
        {
            get
            {
                return this._connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }

        public User GetUser(string userName, string pwd)
        {
            string sqlString = "Select * From ecms_user Where userName='" + userName + "' and userPassword='" + pwd + "'";
            DataSet ds = MySqlAdapter.GetDataSet(this.ConnectionString, System.Data.CommandType.Text, sqlString, null);
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else if (dt.Rows.Count == 1)
            {
                DataRow dr = dt.Rows[0];
                return new User(dr["userName"].ToString(), dr["userID"].ToString());
            }
            return null;
        }

        public bool ExistUserName(string userName)
        {
            string sqlString = "Select * From ecms_user Where userName='" + userName + "'";
            DataSet ds = MySqlAdapter.GetDataSet(this.ConnectionString, System.Data.CommandType.Text, sqlString, null);
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        public bool CreateNewUser(string userName, string pwd)
        {
            if (ExistUserName(userName))
            {
                return false;
            }
            string sqlString = "Insert Into ecms_user (userName,userPassword) Values ('" + userName + "','" + pwd + "')";
            if (MySqlAdapter.ExecuteNonQuery(this.ConnectionString, CommandType.Text, sqlString, null) != 1)
            {
                return false;
            }
            return true;
        }

        public UserInfo GetUserInfo(string userID)
        {
            string sqlString = "Select * From ecms_userinfo Where userID='" + userID + "'";
            DataSet ds = MySqlAdapter.GetDataSet(this.ConnectionString, System.Data.CommandType.Text, sqlString, null);
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else if (dt.Rows.Count == 1)
            {
                DataRow dr = dt.Rows[0];
                bool sex = dr["infoSex"].ToString().Equals("Male");
                return new UserInfo(sex, int.Parse(dr["infoAge"].ToString()), double.Parse(dr["infoWeight"].ToString()), double.Parse(dr["infoHeight"].ToString()));
            }
            return null;
        }

        public bool UpdateUserInfo(string userID, UserInfo userInfo)
        {
            string sex;
            if (userInfo.Sex)
            {
                sex = "Male";
            }
            else
            {
                sex = "Female";
            }
            string sqlString = "Insert Into ecms_userinfo (userID,infoSex,infoAge,infoWeight,infoHeight) Values('" + userID + "','" + sex + "','" + userInfo.Age + "','" + userInfo.Weight + "','" + userInfo.Height +
                "') On Duplicate Key Update infoSex='" + sex + "',infoAge='" + userInfo.Age + "',infoWeight='" + userInfo.Weight + "',infoHeight='" + userInfo.Height + "'";
            if (MySqlAdapter.ExecuteNonQuery(this.ConnectionString, CommandType.Text, sqlString, null) != 1)
            {
                return false;
            }
            return true;
        }
    }
}
